package com.elektronikforumet.camalert;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CameraDatabase {
	public static final String COLUMN_CAMERA_ID = "_id";
	public static final String COLUMN_CAMERA_WEBSERVICE_ID = "cameraWSId";
	public static final String COLUMN_CAMERA_LONGITUDE = "longitude";
	public static final String COLUMN_CAMERA_LATITUDE = "latitude";
	public static final String COLUMN_CAMERA_DIRECTION = "direction";
	public static final String COLUMN_CAMERA_SPEED = "speed";
	public static final String COLUMN_CAMERA_ROAD_NUMBER = "road_number";
	public static final String COLUMN_CAMERA_CAMERA_NUMBER = "camera_number";
	public static final String COLUMN_CAMERA_FILE_ID = "fileId";
	public static final String COLUMN_FILE_ID = "_id"; 
	public static final String COLUMN_FILE_NAME = "name";
	public static final String COLUMN_FILE_DATE = "date";
    
	public static final String DATABASE_NAME = "CameraData";
	public static final String TABLE_CAMERA = "CameraTable";
    public static final String TABLE_FILE = "FileTable";
    
    private static final int DATABASE_VERSION = 4;
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private final Context mCtx;
    private boolean mOpen = false;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        	StringBuilder sb = new StringBuilder();
        	sb.append("create table ");
        	sb.append(TABLE_CAMERA);
        	sb.append(" (");
        	
        	// id
        	sb.append(COLUMN_CAMERA_ID);
        	sb.append(" integer primary key autoincrement, ");
        	
        	sb.append(COLUMN_CAMERA_WEBSERVICE_ID);
        	sb.append(" integer, ");
        	
        	// 
        	sb.append(COLUMN_CAMERA_LATITUDE);
        	sb.append(" real not null, ");
        	
        	// 
        	sb.append(COLUMN_CAMERA_LONGITUDE);
        	sb.append(" real not null, ");
        	
        	// 
        	sb.append(COLUMN_CAMERA_SPEED);
        	sb.append(" integer not null, ");
        	
        	// 
        	sb.append(COLUMN_CAMERA_DIRECTION);
        	sb.append(" integer not null, ");
        	
        	// 
        	sb.append(COLUMN_CAMERA_FILE_ID);
        	sb.append(" integer, ");

        	sb.append(COLUMN_CAMERA_ROAD_NUMBER);
        	sb.append(" text not null, ");
        	
        	sb.append(COLUMN_CAMERA_CAMERA_NUMBER);
        	sb.append(" integer not null);");
        	
        	
            db.execSQL(sb.toString());
            
            // Create Indexes
            sb = new StringBuilder();
            sb.append("CREATE UNIQUE INDEX id_unique ON ");
            sb.append(TABLE_CAMERA);
            sb.append("("+COLUMN_CAMERA_WEBSERVICE_ID+")");
            db.execSQL(sb.toString());
        
            // Not certain that this should be unique
            sb = new StringBuilder();
            sb.append("CREATE UNIQUE INDEX lat_long_unique ON ");
            sb.append(TABLE_CAMERA);
            sb.append("("+COLUMN_CAMERA_LATITUDE+","+COLUMN_CAMERA_LONGITUDE+")");
            db.execSQL(sb.toString());
            
            // File table
            sb = new StringBuilder();
        	sb.append("create table ");
        	sb.append(TABLE_FILE);
        	sb.append(" (");
        	
        	// id
        	sb.append(COLUMN_FILE_ID);
        	sb.append(" integer primary key autoincrement, ");
        	
        	sb.append(COLUMN_FILE_NAME);
        	sb.append(" text not null, ");
        	
        	// 
        	sb.append(COLUMN_FILE_DATE);
        	sb.append(" created_date date default CURRENT_DATE);");
        	db.execSQL(sb.toString());
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO: Add database update script
        	
        	db.execSQL("DROP TABLE IF EXISTS " + TABLE_CAMERA);
        	db.execSQL("DROP TABLE IF EXISTS " + TABLE_FILE);
            onCreate(db);
        }
    }

    public CameraDatabase(Context ctx){
        this.mCtx = ctx;
    }

    public CameraDatabase open() throws SQLException {
    	if(mOpen) {
    		return this;
    	}
    	
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        mOpen = true;
        return this;
    }
    
    public void close() {
    	mOpen = false;
        mDbHelper.close();
    }
    
    public boolean isOpen() {
    	return mOpen;
    }

    public static final int UPDATE_RESULT_FAILED = -1;
    public static final int UPDATE_RESULT_DELETED = 0;
    public static final int UPDATE_RESULT_INSERTED = 1;
    public static final int UPDATE_RESULT_UPDATED = 2;
    
    /**
     * Updates a camera fetched from the webservice, if it exists it will be
     * updated or deleted if the delete-flag is set. If it doesn't exist i will
     * be crated.
     * 
     * @param camera A speedcam from the webservice
     * @return A constant describing what has been done:
     * UPDATE_RESULT_FAILED
     * UPDATE_RESULT_DELETED
     * UPDATE_RESULT_INSERTED
     * UPDATE_RESULT_UPDATED
     */
    public int updateCamera(SpeedCamera camera) {
    	int ret = UPDATE_RESULT_FAILED;
    	if(camera.isDeleted()) {
    		mDb.delete(TABLE_CAMERA, COLUMN_CAMERA_WEBSERVICE_ID+"=?", new String[] { String.valueOf(camera.getId()) });
    		ret = UPDATE_RESULT_DELETED;
    	} else {
    		Cursor c = mDb.query(TABLE_CAMERA, new String[] { COLUMN_CAMERA_WEBSERVICE_ID }, COLUMN_CAMERA_WEBSERVICE_ID+"=?", new String[] {String.valueOf(camera.getId()) }, null, null, null, "0,1");
    		boolean exists = false;
    		if(c.moveToFirst()){
    			exists = true;
    		}
    		c.close();
    		
    		ContentValues cv = new ContentValues();
    		cv.put(COLUMN_CAMERA_LONGITUDE, camera.getLocation().getLongitude());
    		cv.put(COLUMN_CAMERA_LATITUDE, camera.getLocation().getLatitude());
    		cv.put(COLUMN_CAMERA_SPEED, camera.getSpeed());
    		cv.put(COLUMN_CAMERA_DIRECTION, camera.getDirection());
    		cv.put(COLUMN_CAMERA_ROAD_NUMBER, camera.getRoadNumber());
    		cv.put(COLUMN_CAMERA_CAMERA_NUMBER, camera.getCameraNumber());
    		
    		if(exists) {
    			mDb.update(TABLE_CAMERA, cv, COLUMN_CAMERA_WEBSERVICE_ID+"=?", new String[] { String.valueOf(camera.getId()) });
				ret = UPDATE_RESULT_UPDATED;
    		} else {
    			cv.put(COLUMN_CAMERA_WEBSERVICE_ID, camera.getId());
    			mDb.insert(TABLE_CAMERA, "", cv);
    			ret = UPDATE_RESULT_INSERTED;
    		}
    	}
    	
    	return ret;
    }
    
    public int insertCameraFromFile(SpeedCamera camera, int fileId) {
    	int ret = UPDATE_RESULT_FAILED;
    	
    	ContentValues cv = new ContentValues();
		cv.put(COLUMN_CAMERA_LONGITUDE, camera.getLocation().getLongitude());
		cv.put(COLUMN_CAMERA_LATITUDE, camera.getLocation().getLatitude());
		cv.put(COLUMN_CAMERA_SPEED, camera.getSpeed());
		cv.put(COLUMN_CAMERA_DIRECTION, camera.getDirection());
		cv.put(COLUMN_CAMERA_ROAD_NUMBER, camera.getRoadNumber());
		cv.put(COLUMN_CAMERA_CAMERA_NUMBER, camera.getCameraNumber());
		cv.put(COLUMN_CAMERA_FILE_ID, fileId);
		try {
			mDb.insert(TABLE_CAMERA, "", cv);
			ret = UPDATE_RESULT_INSERTED;
		} catch (Exception e) {
			ret = UPDATE_RESULT_FAILED;
		}
		
		return ret;
    }
    
    /**
     * Insert a new file to the database.
     * 
     * @param name Name of the file
     * @return The id of the inserted file
     */
    public int insertFile(String name) {
    	int ret = -1;
    	
    	ContentValues cv = new ContentValues();
		cv.put(COLUMN_FILE_NAME, name);
		try {
			mDb.insert(TABLE_FILE, "", cv);

			Cursor c = mDb.query(TABLE_FILE, new String[] { "last_insert_rowid()" }, null, null, null, null, null);
			if(c.moveToFirst()) {
				ret = c.getInt(0);
			}
		} catch (Exception e) {
		}

		return ret;
    }
    
    /**
     * Deletes an imported file with all cameras originating from that file. 
     * 
     * @param fileId The id of the file
     */
    public void deleteFile(int fileId) {
    	mDb.delete(TABLE_CAMERA, COLUMN_CAMERA_FILE_ID+"=?", new String[] { String.valueOf(fileId)});
    	mDb.delete(TABLE_FILE, COLUMN_FILE_ID+"=?", new String[] { String.valueOf(fileId)});
    }
    
    public Cursor fetchallFiles() {
    	Cursor c = null;
    	
    	try {
    		c = mDb.query(TABLE_FILE, new String[] {COLUMN_FILE_NAME, COLUMN_FILE_DATE, COLUMN_FILE_ID}, null, null, null, null, COLUMN_FILE_DATE);
    	} catch (Exception e) {
    		Log.d("CamAlert", "sql error", e);
		}
    	return c;
    }

    public int countWebserviceCameras() {
    	int ret = -1;
    	Cursor c = null;
    	
    	try {
    		c = mDb.query(TABLE_CAMERA, new String[] { "COUNT("+COLUMN_CAMERA_ID+")" }, COLUMN_CAMERA_FILE_ID+" IS NULL", null, null, null, null);
    		if(c.moveToFirst()) {
    			ret = c.getInt(0);
    		}
    	} catch (Exception e) {
    		Log.d("CamAlert", "sql error", e);
		}
    	
    	return ret;
    }
    
    /**
     * Get all cameras inside a square with the given side in meters
     * centered around a given coordinate.
     * 
     * @param coord Cordinate in the center of the square
     * @param side The side of the square in meters
     * @return ArrayList<SpeedCamera> containing all cameras inside the square
     */
    public ArrayList<SpeedCamera> getCamerasInSquare(Coordinate coord, float side) {
    	
    	String[] cols = new String[] { COLUMN_CAMERA_WEBSERVICE_ID, 
    								   COLUMN_CAMERA_LONGITUDE, 
    								   COLUMN_CAMERA_LATITUDE, 
    								   COLUMN_CAMERA_DIRECTION, 
    								   COLUMN_CAMERA_SPEED, 
    								   COLUMN_CAMERA_ROAD_NUMBER,
    								   COLUMN_CAMERA_CAMERA_NUMBER};
    	
    	
    	StringBuilder selection = new StringBuilder();
    	selection.append(COLUMN_CAMERA_LONGITUDE);
    	selection.append(">? AND ");
    	selection.append(COLUMN_CAMERA_LONGITUDE);
    	selection.append("<? AND ");
    	selection.append(COLUMN_CAMERA_LATITUDE);
    	selection.append(">? AND ");
    	selection.append(COLUMN_CAMERA_LATITUDE);
    	selection.append("<?");
    			   
    	Coordinate[] box = coord.getBoundingBox(side);
    	String[] selectionArgs = new String[] { String.valueOf(box[0].getLongitude()),
    											String.valueOf(box[1].getLongitude()),
    											String.valueOf(box[0].getLatitude()),
    											String.valueOf(box[1].getLatitude()) };
    	
    	Cursor c = mDb.query(TABLE_CAMERA, cols, selection.toString(), selectionArgs, null, null, null);
    	
    	ArrayList<SpeedCamera> cams = new ArrayList<SpeedCamera>();
    	SpeedCamera cam = null;
    	while(c.moveToNext()) {
    		cam = new SpeedCamera();
    		cam.setId(c.getInt(0));
    		cam.setLongitude(c.getFloat(1));
    		cam.setLatitude(c.getFloat(2));
    		cam.setDirection(c.getInt(3));
    		cam.setSpeed(c.getInt(4));
    		cam.setRoadNumber(c.getString(5));
    		cam.setCameraNumber(c.getInt(6));
    		cams.add(cam);
    	}
    	c.close();
    	
        return cams;
    }
}
